package server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import datastructure.PatientRecord;
import datastructure.User;

/**
 * A class which handles the connection to the database.
 * 
 * @author ic08jh7
 * 
 */
public class ConnectionClientJDBC {
	private final String userName, passwd, url;
	private Connection conn;
	private PreparedStatement stmt;
	private ResultSet set;

	/**
	 * Creates a connection to a database.
	 * 
	 * @param userName
	 *            The user name to the database.
	 * @param passwd
	 *            The password to the database.
	 * @param url
	 *            The url to the database.
	 */
	public ConnectionClientJDBC(String userName, String passwd, String url) {
		this.userName = userName;
		this.passwd = passwd;
		this.url = url;
		conn = null;
	}

	/**
	 * Opens a connection to the database with the specified values.
	 * 
	 * @param url
	 *            The address to the database
	 * @param userName
	 *            The username on the database.
	 * @param passwd
	 *            The passwd to the database.
	 * @return true if the connection is established.
	 */
	public boolean openConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + url + "/"
					+ userName, userName, passwd);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database, if any.
	 */
	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
		}
		conn = null;
	}

	/**
	 * Checks if a connection to the database is established.
	 * 
	 * @return true if there is a connection to the database.
	 */
	public boolean isConnected() {
		return conn != null;
	}

	/**
	 * Gets the information about a user in the database with a given SSN.
	 * 
	 * @param ssn
	 *            The SSN of the desired user.
	 * @return a User object of the desired user or null if that user doesn't
	 *         exists.
	 */
	private User getUser(String ssn) {
		try {
			String query = "select * from users where id='" + ssn + "'";
			stmt = conn.prepareStatement(query);
			set = stmt.executeQuery();

			if (set.next()) {
				User u = new User(set.getString("id"), set
						.getString("fullname"), set.getString("phoneNbr"), set
						.getString("authority"), set.getString("division"), set
						.getString("passwd"));
				return u;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

		return null;
	}

	/**
	 * Gets all patient records of a user with a given SSN.
	 * 
	 * @param ssn
	 *            The SSN of the user with the desired patient records.
	 * @return an ArrayList with all patient records of the user or null if
	 *         there where no records of that user.
	 */
	private ArrayList<PatientRecord> getRecordSSN(String ssn) {

		ArrayList<PatientRecord> list = new ArrayList<PatientRecord>();
		String fullName = getUser(ssn).getFullname();
		int recordNbr = -1;
		String date;
		try {
			String query = "select * from patientrecords where id='" + ssn
					+ "'";
			stmt = conn.prepareStatement(query);
			set = stmt.executeQuery();

			while (set.next()) {
				recordNbr = set.getInt("recordNbr");

				date = set.getString("date").replace(':', '.');

				System.out.println("Date i JDBC: " + date);
				PatientRecord p = new PatientRecord(set.getString("id"),
						fullName, set.getString("nurse"), set
								.getString("division"), set
								.getString("symptomes"), set
								.getString("diagnos"));
				p.setRecordNbr(recordNbr);
				p.setDate(date);

				list.add(p);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	private PatientRecord getRecordsFromRecordNbr(String recordNbr) {
		int nbr = -1;
		String date;
		try {
			String query = "select * from patientrecords where recordNbr='"
					+ recordNbr + "'";
			stmt = conn.prepareStatement(query);
			set = stmt.executeQuery();

			if (set.next()) {
				nbr = set.getInt("recordNbr");

				date = set.getString("date").replace(':', '.');

				System.out.println("Date i JDBC: " + date);
				PatientRecord p = new PatientRecord(set.getString("id"), "",
						set.getString("nurse"), set.getString("division"), set
								.getString("symptomes"), set
								.getString("diagnos"));
				p.setRecordNbr(nbr);
				p.setDate(date);

				query = "select fullname from users where id='" + p.getSsn()
						+ "'";
				stmt = conn.prepareStatement(query);

				set = stmt.executeQuery();

				if (set.next()) {
					p.setFullName(set.getString("fullname"));
					return p;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Retrieves the patient record a certain user has access to.
	 * 
	 * @param user
	 *            The user who accesses the patient records.
	 * @return ArrayList<PatientRecord> with the patient records the user have
	 *         access to.
	 */
	public ArrayList<PatientRecord> getAuthorizedRecords(String SSN) {
		System.out.println("Inne i db!!");
		User user = getUser(SSN);
		if (user != null) {
			log("Get authorized records", SSN);
			if (user.getAuthority().toLowerCase().compareTo("patient") == 0) {

				return getRecordSSN(user.getSSN());

			} else if (user.getAuthority().toLowerCase().compareTo("nurse") == 0) {
				System.out.println("nurse");
				ArrayList<PatientRecord> ret = new ArrayList<PatientRecord>();
				ArrayList<String> s = getRecordNbrsfromDivision(user
						.getDivision());
				Iterator<String> itr = s.iterator();

				while (itr.hasNext()) {
					ret.add(getRecordsFromRecordNbr(itr.next()));
				}
				return ret;

			} else if (user.getAuthority().toLowerCase().compareTo("doctor") == 0) {
				ArrayList<PatientRecord> ret = new ArrayList<PatientRecord>();
				ArrayList<String> s = getAllSSN();
				Iterator<String> itr = s.iterator();

				while (itr.hasNext()) {
					ret.addAll(getRecordSSN(itr.next()));
				}
				return ret;

			} else if (user.getAuthority().toLowerCase()
					.compareTo("government") == 0) {
				ArrayList<PatientRecord> ret = new ArrayList<PatientRecord>();
				ArrayList<String> s = getAllSSN();
				Iterator<String> itr = s.iterator();

				while (itr.hasNext()) {
					ret.addAll(getRecordSSN(itr.next()));
				}
				return ret;
			}
		}
		return null;
	}

	/**
	 * Retrieves a list with SSN of all patient with stored patient records.
	 * 
	 * @return ArrayList<String> with the ssn of the patients.
	 */

	private ArrayList<String> getAllSSN() {
		ArrayList<String> SSNlist = new ArrayList<String>();
		try {
			String query = "select distinct id from patientrecords";
			stmt = conn.prepareStatement(query);
			set = stmt.executeQuery();

			while (set.next()) {
				SSNlist.add(set.getString("id"));
			}
			return SSNlist;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Retrives the SSN of all patient within a given division.
	 * 
	 * @param div
	 *            The division to get the patients from.
	 * @return a ArrayList<String> with each patients, with in a specific
	 *         division, ssn.
	 */
	private ArrayList<String> getRecordNbrsfromDivision(String div) {
		ArrayList<String> recordNbrlist = new ArrayList<String>();
		try {
			String query = "select recordNbr from patientrecords where division='"
					+ div + "'";
			stmt = conn.prepareStatement(query);
			set = stmt.executeQuery();

			while (set.next()) {
				String s = set.getString("recordNbr");
				recordNbrlist.add(s);
			}
			return recordNbrlist;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Stores a new patient record in the database.
	 * 
	 * @param r
	 *            is the record to be stored.
	 */
	public boolean append(PatientRecord r, String SSN) {
		User user = getUser(SSN);
		if (user != null) {
			if (user.getAuthority().equalsIgnoreCase("Doctor")) {

				try {
					String query = "insert into patientrecords values(null,'"
							+ r.getSsn() + "','" + r.getNurse() + "','"
							+ r.getDivision() + "','" + r.getSymptomes()
							+ "','" + r.getDiagnos() + "',null)";
					stmt = conn.prepareStatement(query);
					stmt.executeUpdate();

					query = "select recordNbr from patientrecords order by recordNbr desc";
					stmt = conn.prepareStatement(query);
					set = stmt.executeQuery();
					if (set.next()) {
						log("A record with record number "
								+ set.getString("recordNbr")
								+ " has been created", SSN);
					}
					return true;
				} catch (SQLException e) {
					e.printStackTrace();
				}

			}
		}
		return false;
	}

	/**
	 * Deletes a certain record from the database.
	 * 
	 * @param record
	 *            the record to be deleted.
	 */
	public boolean deleteRecord(int recordNbr, String ssn) {
		User user = getUser(ssn);
		if (user != null) {
			System.out.println("User not null");
			if (user.getAuthority().equalsIgnoreCase("Government")) {
				System.out.println("auktoritit = gov");
				try {
					String query = "delete from patientrecords where recordNbr ="
							+ recordNbr;

					stmt = conn.prepareStatement(query);
					stmt.executeUpdate();
					log("Record " + recordNbr + " was deleted", ssn);
					return true;
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return false;
	}

	/**
	 * Deletes all records which belongs to a user with a specific SSN.
	 * 
	 * @param pssn
	 *            The SSN of the user which records are to be deleted.
	 */
	public boolean deleteAllRecords(String pssn, String ussn) {

		User user = getUser(ussn);
		if (user != null) {
			if (user.getAuthority().equalsIgnoreCase("Government")) {
				try {

					String query = "delete from patientrecords where id ='"
							+ pssn + "'";
					stmt = conn.prepareStatement(query);
					stmt.executeUpdate();
					if (getRecordSSN(pssn).size() == 0) {
						log("All records to patient " + pssn + " was deleted",
								ussn);
						return true;
					} else {
						log("Failed to delete records from " + pssn
								+ ", no record exists", ussn);
					}

				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return false;
	}

	/**
	 * Returns the hash value which corresponds to the given user.
	 * 
	 * @param ssn
	 *            The SSN of the user which hash value is desired.
	 * @return the hash of the user with a given SSN.
	 */
	public String getHash(String ssn) {
		try {

			String query = "select passwd from users where id ='" + ssn + "'";
			System.out.println(ssn);
			stmt = conn.prepareStatement(query);
			set = stmt.executeQuery();
			if (set.next()) {
				String test = set.getString("passwd");
				System.out.println("Get hash out: " + test);
				System.out.flush();
				// log("Passwd hash to user was sent", ssn);
				return test;
			}
			// log("No hash was found", ssn);

		} catch (SQLException e) {
			System.out.println("Misslyckades med try, i catch");
			e.printStackTrace();
		}
		System.out.println("Misslyckades med try");
		return null;
	}

	/**
	 * Logs and puts a timestamp on the log.
	 * 
	 * @param event
	 *            An explanation for the log.
	 * @param ssn
	 *            The users whom provoked the log.
	 */
	public void log(String event, String ssn) {
		try {
			String query = "insert into log values(null,'" + event + "',null,'"
					+ ssn + "')";
			stmt = conn.prepareStatement(query);
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
